package com.pro.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;

import com.pro.entity.NewsEntity;
import com.pro.util.DBManager;
import com.pro.util.DateUtil;
import com.pro.util.QueryResult;

public class NewsDaoImpl {
	//nid 100 显示在首页  0 不显示 其他 根据新闻分类id查询
	public ArrayList<NewsEntity> newsAll(int nid){
		ArrayList<NewsEntity> alist= new ArrayList<NewsEntity>();
	    String sqlString=null;
		if(nid==100) {
	     sqlString="select id,create_time,news_title,content,nid,show_home from news" 
	     		+ " where show_home = 1";
		}else if(nid==0) {
			sqlString="select id,create_time,news_title,content,nid,show_home from news"
		    		+ " where show_home = 0 order by create_time desc";
		}else {
			sqlString="select id,create_time,news_title,content,nid,show_home from news"
		    		+ " where nid = '"+nid+"' order by create_time desc";
		}
	    QueryResult rSet=DBManager.executeQuery(sqlString);
	      if (rSet!=null) {
	        try {
	          while (rSet.rs.next()) {
	        	  alist.add(new NewsEntity(rSet.rs.getInt("id"),
	            		rSet.rs.getDate("create_time"),
	            		rSet.rs.getString("news_title"),
	            		rSet.rs.getString("content"),
	            		rSet.rs.getInt("nid"),
	            		rSet.rs.getInt("show_home")));
	            		
	          }
	        } catch (SQLException e) {
	          e.printStackTrace();
	        }
	      }
	      rSet.close();
	    return alist;
	}
    //实现分页 并和新闻分类表连接 并且根据条件进行查询
	public ArrayList<NewsEntity> newsAdminList(Integer page,Integer limit,String nid,
			String showHome,String beginDate,String endDate) {
		ArrayList<NewsEntity> alist = new ArrayList<NewsEntity>();
		String sqlString = "select n.id,n.create_time,n.news_title,n.content,n.nid,n.show_home,nt.name from news n,news_type nt " + 
				"where n.nid = nt.id ";
		if(nid!=null&&!nid.equals("")) {
			sqlString = sqlString + " and n.nid = '"+nid+"'";
		}
		if(showHome!=null&&!showHome.equals("")) {
			sqlString = sqlString + " and n.show_home = '"+showHome+"'";
		}
		if(beginDate!=null&&!beginDate.equals("")) {
			sqlString = sqlString + " and n.create_time >= '"+beginDate+"'";
		}
		if(endDate!=null&&!endDate.equals("")) {
			sqlString = sqlString + " and n.create_time <= '"+endDate+"'";
		}
		sqlString = sqlString+" order by n.create_time desc";
		if(page!=null&&limit!=null) {
			sqlString+= " limit "+(page-1)*limit+","+limit;
		}
		System.out.println(sqlString);
	    QueryResult rSet=DBManager.executeQuery(sqlString);
	      if (rSet!=null) {
	        try {
	          while (rSet.rs.next()) {
	        	  alist.add(new NewsEntity(rSet.rs.getInt("id"),
	            		rSet.rs.getDate("create_time"),
	            		rSet.rs.getString("news_title"),
	            		rSet.rs.getString("content"),
	            		rSet.rs.getInt("nid"),
	            		rSet.rs.getInt("show_home"),
	            		rSet.rs.getString("name")));
	            		
	          }
	        } catch (SQLException e) {
	          e.printStackTrace();
	        }
	      }
	      rSet.close();
	    return alist;
	}
	
	//根据新闻id进行查询 进行时间倒序排序
	public NewsEntity newsDetail(int id){
		NewsEntity newsEntity = null;
	    String sqlString="select id,create_time,news_title,content,nid,show_home from news"
		    		+ " where id = '"+id+"' order by create_time desc";
	    QueryResult rSet=DBManager.executeQuery(sqlString);
	      if (rSet!=null) {
	        try {
	          while (rSet.rs.next()) {
	        	  newsEntity = new NewsEntity(rSet.rs.getInt("id"),
	            		rSet.rs.getDate("create_time"),
	            		rSet.rs.getString("news_title"),
	            		rSet.rs.getString("content"),
	            		rSet.rs.getInt("nid"),
	            		rSet.rs.getInt("show_home"));
	            		
	          }
	        } catch (SQLException e) {
	          e.printStackTrace();
	        }
	      }
	      rSet.close();
	    return newsEntity;
	}
	/**
	 * 
	 * @param nid 产品id
	 * @param state 0上一篇 1下一篇
	 * @return
	 */
	public NewsEntity newsPreAndNext(int nid,int state){
		NewsEntity newsEntity = null;
	    String sqlString=null;
		if(state == 0) {
	     sqlString="SELECT id,create_time,news_title,content,nid,show_home FROM news " + 
	     		"where id < '"+nid+"' ORDER BY id DESC LIMIT 1";
		}else if(state == 1) {
			sqlString="SELECT id,create_time,news_title,content,nid,show_home FROM news " + 
					"where id > '"+nid+"' LIMIT 1";
		}
	    QueryResult rSet=DBManager.executeQuery(sqlString);
	      if (rSet!=null) {
	        try {
	          while (rSet.rs.next()) {
	        	  newsEntity = new NewsEntity(rSet.rs.getInt("id"),
	            		rSet.rs.getDate("create_time"),
	            		rSet.rs.getString("news_title"),
	            		rSet.rs.getString("content"),
	            		rSet.rs.getInt("nid"),
	            		rSet.rs.getInt("show_home"));
	            		
	          }
	        } catch (SQLException e) {
	          e.printStackTrace();
	        }
	      }
	      rSet.close();
	    return newsEntity;
	}
	
	public int newsUpdate(NewsEntity nEntity) {
 		String sqlString="update news set create_time='"+DateUtil.DateToDate(new Date())+"',news_title='"+nEntity.getNewsTitle()+"',content='"+nEntity.getContent()+"',nid='"+nEntity.getNid()+"',show_home='"+nEntity.getShowHome()+"' where id = '"+nEntity.getId()+"'";
 		 int result= DBManager.executeUpdate(sqlString);
 		return result;
 	}
	
	 public int newsAdd(NewsEntity nEntity) {
 		String sqlString="insert into news(create_time,news_title,content,nid,show_home) values('"+DateUtil.DateToDate(new Date())+"','"+nEntity.getNewsTitle()+"','"+nEntity.getContent()+"','"+nEntity.getNid()+"','"+nEntity.getShowHome()+"')";
 		 int result= DBManager.executeUpdate(sqlString);
 		return result;
 	}
	 
	 public int newsDelete(int id) {
			// TODO Auto-generated method stub
			String sqlString = "delete from news where id = '"+id+"'";
			int result = DBManager.executeUpdate(sqlString);
			return result;
		}
	 
}
